package org.example.taskmaster.database;

import java.util.Calendar;

import org.example.taskmaster.Task;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseAdapter {

	// Fields for table containing names of lists
	private static final String LIST_TABLE = "lists";
	public static final String KEY_LISTROWID = "_id";
	public static final String KEY_LISTNAME = "name";
	// Fields for table containing tasks
	private static final String TASK_TABLE = "tasks";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_LIST = "list";
	public static final String KEY_YEAR = "year";
	public static final String KEY_MONTH = "month";
	public static final String KEY_DAY = "day";
	public static final String KEY_HOUR = "hour";
	public static final String KEY_MINUTE = "minute";
	public static final String KEY_NOTES = "notes";
	public static final String KEY_COMPLETE = "complete";

	private Context context;
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;

	
	private Calendar RightNow;
	private int CurrentYear;
	private String yearString;
	private int CurrentMonth;
	private String monthString;
	private int CurrentDay;
	private String dayEnd;
	
	public DatabaseAdapter(Context context) {
		this.context = context;
	}

	public DatabaseAdapter open() throws SQLException {
		dbHelper = new DatabaseHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();        //lv.setStrings(columns);
	}

	/* ********************
	 * Task-related methods
	 * ********************/
	/**
	 * Create a new task.
	 * If the task is successfully created return the new
	 * rowId for that note, otherwise return a -1 to indicate failure.
	 */
	public long createTask(String name, String list, String notes,int year, int month, int day, int hour, int minute, int complete) {
		// TODO: catch invalid values, such as empty name
		
		// create and insert the new entry into TASK_TABLE
		ContentValues initialValues = createContentValues(name, list, notes, year, month, day, hour, minute, complete);
		return database.insert(TASK_TABLE, null, initialValues);
	}
	/**
	 * Update a task
	 */
	public boolean updateTask(long rowId, String name, String list, String notes, int year, int month, int day, int hour, int minute, int complete) {
		ContentValues updateValues = createContentValues(name, list, notes, year, month, day, hour, minute, complete);

		return database.update(TASK_TABLE, updateValues, KEY_ROWID + "="
				+ rowId, null) > 0;
	}
	/**
	 * Deletes task
	 */
	public boolean deleteTask(long rowId) {
		return database.delete(TASK_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Get cursor over tasks which are due seven days forward or earlier
	 */
	public Cursor fetchUpcomingTasks(String orderBy, boolean showComplete){
		// Produce part of query to handle completed tasks
		String complete = "";
		if (showComplete == false) {
			complete = " AND " + KEY_COMPLETE + "= 0";  
		}
		// handle time interactions
	    RightNow = Calendar.getInstance();
	    CurrentYear = RightNow.get(Calendar.YEAR);
	    yearString = Integer.toString(CurrentYear);
	    CurrentMonth = RightNow.get(Calendar.MONTH);
	    CurrentMonth += 1;
	    monthString = Integer.toString(CurrentMonth);
	    CurrentDay = RightNow.get(Calendar.DAY_OF_MONTH);
	    CurrentDay += 7;
	    dayEnd = Integer.toString(CurrentDay);
	    return database.query(TASK_TABLE, new String[] {
				KEY_ROWID, KEY_NAME, KEY_LIST, KEY_NOTES, KEY_YEAR, KEY_MONTH, KEY_DAY, KEY_HOUR, KEY_MINUTE, KEY_COMPLETE  },
				KEY_YEAR + "<=" + yearString + " AND " + KEY_MONTH + "<=" + monthString + " AND " + KEY_DAY + "<=" + dayEnd + complete, 
				null, null, null, orderBy, null);
	}

	/**
	 * Return a Cursor over the list of all tasks in the database
	 */
	public Cursor fetchAllTasks(boolean showComplete) {
		return fetchAllTasks(null, showComplete);
	}
	public Cursor fetchAllTasks(String orderBy, boolean showComplete) {
		String complete = "";
		if (showComplete == false) {
			complete = KEY_COMPLETE + "= 0";  
		}

		return database.query(TASK_TABLE, new String[] { 
				KEY_ROWID,KEY_NAME, KEY_LIST, KEY_NOTES, KEY_YEAR, KEY_MONTH, KEY_DAY, KEY_HOUR, KEY_MINUTE, KEY_COMPLETE},
				complete, null, null, null, orderBy);
	}

	/**
	 * Return a Cursor over the list of all tasks in the given list
	 */
	public Cursor fetchTasksByList(String listName, boolean showComplete) {
		return fetchTasksByList(listName, null, showComplete);
	}
	public Cursor fetchTasksByList(String listName, String orderBy, boolean showComplete) {
		// for All Tasks or Upcoming Tasks, redirect to special methods
		if (listName == null) {return this.fetchAllTasks(orderBy, showComplete);}
		else if (listName == "upcoming"){return this.fetchUpcomingTasks(orderBy, showComplete);}
		// Produce part of query to handle completed tasks
		String complete = "";
		if (showComplete == false) {
			complete = " AND " + KEY_COMPLETE + "= 0";  
		}
		// make database query
		return database.query(TASK_TABLE, new String[] {
				KEY_ROWID, KEY_NAME, KEY_LIST, KEY_NOTES, KEY_YEAR, KEY_MONTH, KEY_DAY, KEY_HOUR, KEY_MINUTE, KEY_COMPLETE},
				KEY_LIST + "=\"" + listName + "\" " + complete, null, null, null, orderBy, null);
	}
	
	
	private ContentValues createContentValues(String name, String list, String notes,int year, int month, int day, int hour, int minute, int complete) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_LIST, list);
		values.put(KEY_NOTES, notes);
		values.put(KEY_YEAR, year);
		values.put(KEY_MONTH, month);
		values.put(KEY_DAY, day);
		values.put(KEY_HOUR, hour);
		values.put(KEY_MINUTE, minute);
		values.put(KEY_COMPLETE, complete);
		return values;
	}
	public Task getTask(long rowId){
		Task task = null;
		Cursor mCursor = database.query(true, TASK_TABLE, new String[] {
				KEY_ROWID, KEY_NAME, KEY_LIST, KEY_NOTES, KEY_YEAR, KEY_MONTH, KEY_DAY, KEY_HOUR, KEY_MINUTE, KEY_COMPLETE },
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
			task = new Task(mCursor.getString(1));
			task.setList(mCursor.getString(2));
			task.setNotes(mCursor.getString(3));
			task.setDueYear(mCursor.getInt(4));
			task.setDueMonth(mCursor.getInt(5));
			task.setDueDay(mCursor.getInt(6));
			task.setDueHour(mCursor.getInt(7));
			task.setDueMinute(mCursor.getInt(8));
			task.setComplete(mCursor.getInt(9));
		}

		return task;
	}
	/**
	 * Return a Cursor positioned at the defined task
	 */
	public Cursor fetchTask(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, TASK_TABLE, new String[] {
				KEY_ROWID, KEY_NAME, KEY_LIST, KEY_NOTES, KEY_YEAR, KEY_MONTH, KEY_DAY, KEY_HOUR, KEY_MINUTE, KEY_COMPLETE  },
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public void toggleComplete(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, TASK_TABLE, new String[] {
				KEY_ROWID, KEY_NAME, KEY_LIST, KEY_NOTES, KEY_YEAR, KEY_MONTH, KEY_DAY, KEY_HOUR, KEY_MINUTE, KEY_COMPLETE },
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
			int isComplete = mCursor.getInt(9);
			// switch value from 1 to 0 or reverse
			isComplete = (-1) * (isComplete - 1);
			// update task
			updateTask(rowId, mCursor.getString(1), mCursor.getString(2), mCursor.getString(3), mCursor.getInt(4), mCursor.getInt(5), mCursor.getInt(6),
					mCursor.getInt(7), mCursor.getInt(8), isComplete);
		}

	}

	/* ********************
	 * List-related methods
	 * ********************/
	/**
	 * Create a new list If the list is successfully created return the new
	 * rowId for that list, otherwise return a -1 to indicate failure.
	 */
	public long createList(String name) {
		// fail if name is an empty string
		if (name.equals("")) { 
			return -1;
		}
		
		// check for duplicate name
		Cursor c = database.query(LIST_TABLE, new String[] {
				KEY_LISTROWID, KEY_LISTNAME},
				KEY_LISTNAME + "=\"" + name + "\"", null, null, null, null, null);
		if (c.getCount() > 0) {
			return -2;
		}
		
		// create and insert the new entry into LIST_TABLE
		ContentValues initialValues = createListContentValues(name);
		return database.insert(LIST_TABLE, null, initialValues);
	}
	/**
	 * Deletes list
	 */
	public boolean deleteList(String list) {
		return database.delete(LIST_TABLE, KEY_LISTNAME + "=" + "\""+list+"\"", null) > 0;
	}
	/**
	 * Return a Cursor over the list of all lists in the database
	 */
	public Cursor fetchAllLists() {
		return database.query(LIST_TABLE, new String[] { KEY_LISTROWID, 
				KEY_LISTNAME }, null, null, null, null, null);
	}
	
	private ContentValues createListContentValues(String name) {
		ContentValues values = new ContentValues();
		values.put(KEY_LISTNAME, name);
		return values;
	}
}
